Skip to main content link. Accesskey S
  • Help
  • HCL Logo
  • HCL Notes and Domino Application Development wiki
  • THIS WIKI IS READ-ONLY. Individual names altered for privacy purposes.
  • HCL Forums and Blogs
  • Home
  • Product Documentation
  • Community Articles
  • Learning Center
  • API Documentation
Search
Community Articles > Programming > A shortcut for get the number of rows used in Excelfile/Worksheet
  • Share Show Menu▼
  • Subscribe Show Menu▼

Recent articles by this author

Traversing XML result of AJAX request with JQuery

When working with JQuery and Ajax, there is simply no simple good instructions on how to traverse the complete tree of an XML result of an Ajax call. This is how You do it.

A shortcut for get the number of rows used in Excelfile/Worksheet

When working with Excelfiles it is very common to know how many rows there is to process in the file so that You can inform the user in a progressbar or something. I've seen a numerous examples of simple looping through each row until the ActiveCell is a nullvalue (empty string). This is not very ...

How to create and send an email message containing an HTML body using an agent

To create an email that contains a HTML body is a quite simple process, but there is a lot to think about because of the variety of emailclients and their specific behaviour in how they implement the HTMLCSS specifications. I will here try to guide You through the complete steps needed to ...

Using XML response from Ajax call in Lotus Domino Web applications

There is a lot to say when it comes to AJAX! If You have read the article "Integrating Ajax into traditional IBM Lotus Domino Web applications" (http:www-10.lotus.comlddddwiki.nsfdxIntegratingAjaxintotraditionalIBMLotusDominoWebapplications) You will know how to use the Ajax response ...

HowTo: Create Bullet List in Word Document

I'm continuing the discussion on how to create a word document. Now it's time to understand how to format text in Word; This time I'll show how to write Bullet Lists into a word document. First, read up on how to create a word document here: ...
Community articleA shortcut for get the number of rows used in Excelfile/Worksheet
Added by ~Richard Zekboosilyettu | Edited by ~Richard Zekboosilyettu on October 1, 2012 | Version 3
  • Actions Show Menu▼
expanded Abstract
collapsed Abstract
No abstract provided.
Tags: excel, rowcount, lotusscript
When working with Excelfiles it is very common to know how many rows there is to process in the file so that You can inform the user in a progressbar or something. I've seen a numerous examples of simple looping through each row until the ActiveCell is a nullvalue (empty string). This is not very efficient if the file is very large. There is a shorter way, already given in the Excel object.
 
 
	Dim FileName As String
	 
	Dim objExcel As Variant
	Set objExcel = CreateObject("Excel.Application")
	FileName = "C:\myExcelfile.xls"
	 
	Call objExcel.Workbooks.Open(FileName)
	Dim RowNumber As Long
	 
	RowNumber = objExcel.Sheets(1).UsedRange.Rows.Count
	MsgBox "Rowcount = " + CStr(RowNumber)
	 
	Call objExcel.ActiveWorkbook.Close
	objExcel.Quit
	Set objExcel = Nothing
	 
	
 
 
It is simply as that... 

Another tip on almost the same topic: In the same way as You can get the number of rows used, You can get the number of COLUMNS used by simply switching the "Rows" to "Columns":

ColumnNumber = objExcel.Sheets(1).UsedRange.Columns.Count

So, now You now exactly how many cells You have to work with...


  • Actions Show Menu▼


expanded Attachments (0)
collapsed Attachments (0)
Edit the article to add or modify attachments.
expanded Versions (3)
collapsed Versions (3)
Version Comparison     
VersionDateChanged by              Summary of changes
This version (3)Oct 1, 2012, 8:30:22 AM~Richard Zekboosilyettu  Added information about Columns.Count
2Aug 23, 2011, 11:55:41 AM~Vanessa Preponelyynds  Spelling corrections
1Aug 23, 2011, 11:54:04 AM~Vanessa Preponelyynds  
expanded Comments (0)
collapsed Comments (0)
Copy and paste this wiki markup to link to this article from another article in this wiki.
Go ElsewhereStay ConnectedAbout
  • HCL Software
  • HCL Digital Solutions community
  • HCL Software support
  • BlogsDigital Solutions blog
  • Community LinkHCL Software forums and blogs
  • About HCL
  • Privacy
  • Accessibility